package com.koron.util;

import com.koron.util.TemplateConfig.BeanConfig;
import com.koron.util.TemplateConfig.BusiImplConfig;
import com.koron.util.TemplateConfig.MapperConfig;
import org.apache.commons.lang3.StringUtils;

import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class TemplateUtils {

	private static final String URL = "jdbc:mysql://192.168.4.169:3306/css_public?useUnicode=true&AutoReconnect=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai";

	private static final String USERNAME = "root";

	private static final String PASSWORD = "water";

	public static void generateQueryBeanFile(BeanConfig config) throws IOException {
		Map<String, Object> data = new HashMap<String, Object>();
		ClassInfo classInfo = getClassInfo(config.getTableName());
		config.setClassName(getInitialsUpper(classInfo.getClssName()));
		config.setFileSuffix("java");
		config.setModuleType("QueryBean");
		data.put("author", config.getAuthor());
		data.put("moduleName", config.getModuleName());
		data.put("comments", config.getComments());
		data.put("className", config.getClassName());
		data.put("fields", classInfo.getFields());
		FreeMarkerTemplateUtils.generate(config, data, "query-bean.ftl");
	}

	public static void generateBeanFile(BeanConfig config) throws IOException {
		Map<String, Object> data = new HashMap<String, Object>();
		ClassInfo classInfo = getClassInfo(config.getTableName());
		config.setClassName(getInitialsUpper(classInfo.getClssName()));
		config.setFileSuffix("java");
		config.setModuleType("Bean");
		data.put("author", config.getAuthor());
		data.put("moduleName", config.getModuleName());
		data.put("comments", config.getComments());
		data.put("className", config.getClassName());
		data.put("fields", classInfo.getFields());
		FreeMarkerTemplateUtils.generate(config, data, "bean.ftl");
	}

	public static void generateMapperFile(MapperConfig config) {
		Map<String, Object> data = new HashMap<String, Object>();
		ClassInfo classInfo = getClassInfo(config.getTableName());
		config.setClassName(getInitialsUpper(classInfo.getClssName()));
		config.setFileSuffix("java");
		config.setModuleType("Mapper");
		data.put("author", config.getAuthor());
		data.put("moduleName", config.getModuleName());
		data.put("comments", config.getComments());
		data.put("className", config.getClassName());
		FreeMarkerTemplateUtils.generate(config, data, "mapper.ftl");
	}

	public static void generateBusiImplFile(BusiImplConfig config) {
		Map<String, Object> data = new HashMap<String, Object>();
		ClassInfo classInfo = getClassInfo(config.getTableName());
		config.setClassName(getInitialsUpper(classInfo.getClssName()));
		config.setFileSuffix("java");
		data.put("author", config.getAuthor());
		data.put("moduleName", config.getModuleName());
		data.put("comments", config.getComments());
		data.put("className", config.getClassName());
		data.put("busicode", config.getBusiCode());
		config.setClassName(config.getBusiCode());
		FreeMarkerTemplateUtils.generate(config, data, config.getTemplateName());
	}

	public static String toSelectSql(String prefix, String tableName) {
		DataTableInfo table = getTableMetaData(tableName);
		List<ColumnInfo> columns = table.getColumns();
		StringBuilder strBuil = new StringBuilder();
		strBuil.append("SELECT \n");
		for (ColumnInfo column : columns) {
			if (StringUtils.isNotEmpty(prefix)) {
				strBuil.append(prefix);
				strBuil.append(".");
			}
			strBuil.append(column.getName());
			strBuil.append(",");
			strBuil.append("\n");
		}
		strBuil.append(" FROM ");
		strBuil.append(table.getName());
		if (StringUtils.isNotEmpty(prefix)) {
			strBuil.append("\t");
			strBuil.append(prefix);
		}
		return strBuil.toString();
	}

	public static String toInsertSql(String tableName) {
		DataTableInfo table = getTableMetaData(tableName);
		List<ColumnInfo> columns = table.getColumns();
		StringBuilder strBuil = new StringBuilder();
		strBuil.append("INSERT INTO ");
		strBuil.append(table.getName());
		strBuil.append("\t");
		strBuil.append("(");
		strBuil.append("\n");
		for (ColumnInfo column : columns) {
			strBuil.append(column.getName());
			strBuil.append(",");
			strBuil.append("\n");
		}

		String val = strBuil.toString();
		val.substring(0, val.length() - 1);
		strBuil.delete(0, strBuil.length());
		strBuil.append(val);
		strBuil.append(") VALUES (");
		strBuil.append("\n");
		for (ColumnInfo column : columns) {
			strBuil.append("#{");
			strBuil.append(column.getFieldName());
			strBuil.append(",");
			strBuil.append("jdbcType=");
			strBuil.append(getJdbcType(column.getType()));
			strBuil.append("}");
			strBuil.append(",");
			strBuil.append("\n");
		}
		val = strBuil.toString();
		if (val.endsWith(",")) {
			val = val.substring(0, val.length() - 1);
			strBuil.delete(0, strBuil.length());
			strBuil.append(val);
		}
		strBuil.append(")");
		return strBuil.toString();
	}

	public static String toFieldString(String tableName) {
		ClassInfo classInfo = getClassInfo(tableName);
		List<ClassFieldInfo> fields = classInfo.getFields();
		StringBuilder strBuil = new StringBuilder();
		for (ClassFieldInfo field : fields) {
			strBuil.append("/**");
			strBuil.append(field.getComment());
			strBuil.append("*/");
			strBuil.append("\n");
			strBuil.append("private\t");
			strBuil.append(field.getType());
			strBuil.append("\t");
			strBuil.append(field.getName());
			strBuil.append(";\n");
		}
		return strBuil.toString();
	};

	public static ClassInfo getClassInfo(String tableName) {
		ClassInfo classInfo = new ClassInfo();
		DataTableInfo table = getTableMetaData(tableName);
		classInfo.setClssName(getHumpName(table.getName()));
		List<ColumnInfo> columns = table.getColumns();
		List<ClassFieldInfo> fields = new ArrayList<ClassFieldInfo>();
		classInfo.setFields(fields);
		for (ColumnInfo column : columns) {
			ClassFieldInfo field = new ClassFieldInfo();
			field.setColumnName(column.getName());
			field.setComment(column.getComment());
			field.setName(column.getFieldName());
			field.setType(getJavaType(column.getType()));
			fields.add(field);
		}
		return classInfo;
	}

	public static String toUpdateSql(String tableName) {
		DataTableInfo table = getTableMetaData(tableName);
		List<ColumnInfo> columns = table.getColumns();
		StringBuilder strBuil = new StringBuilder();
		strBuil.append("UPDATE ");
		strBuil.append(table.getName());
		strBuil.append("\t");
		strBuil.append("SET\t\n");
		for (ColumnInfo column : columns) {
			strBuil.append(column.getName());
			strBuil.append("\t=\t");
			strBuil.append("#{");
			strBuil.append(column.getFieldName());
			strBuil.append(",");
			strBuil.append("jdbcType=");
			strBuil.append(getJdbcType(column.getType()));
			strBuil.append("}");
			strBuil.append(",");
			strBuil.append("\n");
		}
		String val = strBuil.toString();
		if (val.endsWith(",")) {
			val = val.substring(0, val.length() - 1);
			strBuil.delete(0, strBuil.length());
			strBuil.append(val);
		}
		strBuil.append(")");
		return strBuil.toString();
	}

	private static DataTableInfo getTableMetaData(String tableName) {
		PreparedStatement pst = null;
		ResultSet rs = null;
		Connection connection = null;
		DataTableInfo table = new DataTableInfo();
		List<ColumnInfo> columns = new ArrayList<ColumnInfo>();
		table.setColumns(columns);
		try {
			connection = getConnection();
			pst = connection.prepareStatement("select table_name, column_name, data_type, column_comment  from information_schema.COLUMNS where TABLE_SCHEMA = (select database()) and TABLE_NAME = ?");
			pst.setString(1, tableName);
			rs = pst.executeQuery();
			while (rs.next()) {
				if (StringUtils.isEmpty(table.getName())) {
					table.setName(rs.getString("table_name"));
				}
				ColumnInfo column = new ColumnInfo();
				column.setComment(rs.getString("column_comment"));
				column.setName(rs.getString("column_name"));
				column.setType(rs.getString("data_type"));
				column.setFieldName(getHumpName(column.getName().toLowerCase()));
				columns.add(column);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (pst != null) {
				try {
					pst.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return table;
	}

	private static Connection getConnection() throws ClassNotFoundException, SQLException {
		Class.forName("com.mysql.jdbc.Driver");
		return DriverManager.getConnection(URL, USERNAME, PASSWORD);
	}

	private static String getHumpName(String columnName) {
		if (StringUtils.isEmpty(columnName)) {
			return StringUtils.EMPTY;
		}
		StringBuilder sb = new StringBuilder(columnName);
		Matcher mc = Pattern.compile("_").matcher(columnName);
		int i = 0;
		while (mc.find()) {
			int position = mc.end() - (i++);
			sb.replace(position - 1, position + 1, sb.substring(position, position + 1).toUpperCase());
		}
		return sb.toString();
	}

	private static String getInitialsUpper(String str) {
		StringBuilder sb = new StringBuilder(str);
		sb.replace(0, 1, str.substring(0, 1).toUpperCase());
		return sb.toString();
	}

	private static String getJavaType(String jdbcType) {
		TypeEnum type = TypeEnum.valueOf(jdbcType.toUpperCase());
		return type == null ? StringUtils.EMPTY : type.getJavaType();
	}

	private static String getJdbcType(String jdbcType) {
		TypeEnum type = TypeEnum.valueOf(jdbcType.toUpperCase());
		return type == null ? StringUtils.EMPTY : type.getJdbcType();
	}

	public static void main(String[] args) throws IOException {
		
		/*DeleteBusiImplConfig beanConfig = new DeleteBusiImplConfig();
		beanConfig.setAuthor("管理员");
		beanConfig.setComments("实体类");
		beanConfig.setModuleName("accountingAdjust.impl");
		beanConfig.setBusiCode("InternetCloseDelete");
		beanConfig.setTableName("BILL_INTERNET_CLOSE");
		//generateBusiImplFile(beanConfig);
*/		
		System.out.println(toInsertSql("tbltree"));
		
	}

}
